package com.lxl.workbench.dao;

import com.lxl.commons.utils.DbUtil;
import com.lxl.settings.entity.User;
import com.lxl.workbench.entity.CommonStaff;
import com.lxl.workbench.entity.Curator;
import com.lxl.workbench.entity.Library;
import javax.servlet.jsp.jstl.sql.Result;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 北上寻海
 */
public class LibraryDaoImpl implements LibraryDao{

    @Override
    public void insertLibrary(User user, Curator curator, Library library) throws SQLException {
        String librarySql = "insert into tbl_library values('"+library.getId()+"','"+library.getLibraryName()+"','"+library.getLibraryLocation()+"','"+library.getLibraryCurator()+"')";
        String curatorSql = "insert into tbl_curator values('"+curator.getId()+"','"+curator.getCuratorName()+"','"+curator.getGender()+"','"+curator.getPhone()+"','"+curator.getEmail()+"','"+curator.getUserId()+"','"+curator.getPermissions()+"')";
        String userSql = "insert into tbl_user values('"+user.getId()+"','"+user.getUsername()+"','"+user.getPassword()+"')";
        DbUtil.update(userSql, curatorSql, librarySql);
    }

    @Override
    public void updateLibrary(Library library,Curator curator,User user) throws SQLException {
         String librarySql = "update tbl_library set library_name = '"+library.getLibraryName()+"',library_location='"+library.getLibraryLocation()+"' where id = '"+library.getId()+"'";
         String curatorSql = "update tbl_curator set staff_name = '"+curator.getCuratorName()+"',gender = '"+curator.getGender()+"',phone = '"+curator.getPhone()+"',email = '"+curator.getEmail()+"' where id = '"+curator.getId()+"'";
         String userSql = "update tbl_user set user_name = '"+user.getUsername()+"',password = '"+user.getPassword()+"' where id = '"+user.getId()+"'";
         DbUtil.update(librarySql,curatorSql,userSql);
    }
    @Override
    public int deleteLibraryByIds(String[] delIds) throws SQLException {
        StringBuilder sql = new StringBuilder("delete from tbl_library where id in ");
        sql.append("(");
        for (String delId : delIds) {
            sql.append("'").append(delId).append("',");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(")");
        return  DbUtil.update(sql.toString());
    }


    @Override
    public Result selectLibraryCondition(String libraryName, String libraryLocation) throws SQLException {
        //动态拼接sql语句
        String sql = "select l.id,l.library_name,l.library_location,c.staff_name as library_curator from tbl_library l join tbl_curator c on l.library_curator = c.id";
        //先定义一个list集合来存放sql语句
        List<String> list = new ArrayList<>();
        if(libraryName!=null&& !"".equals(libraryName)){
            String sql1 = "library_name like '%"+libraryName+"%'";
            list.add(sql1);
        }
        if(libraryLocation!=null&& !"".equals(libraryLocation)){
            String sql2 = "library_location like '%"+libraryLocation+"%'";
            list.add(sql2);
        }

        if(list.size()>0){
            //list转数组+String类的拼接
            sql += " where " + String.join(" and ", list.toArray(new String[0]));
        }
        //调用DbUtil的查询方法
        return DbUtil.select(sql);
    }

    @Override
    public Result selectLibraryById(String id) throws SQLException {
        String sql = "select id,library_name,library_location,library_curator from tbl_library where id = '"+id+"' ";
        return DbUtil.select(sql);
    }

    @Override
    public Result selectLibraryByIds(String[] ids) throws SQLException {
        StringBuilder sql = new StringBuilder("select id,library_name,library_location,library_curator from tbl_library where id in");
        sql.append("(");
        for (String id : ids) {
            sql.append("'").append(id).append("',");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(")");
        return  DbUtil.select(sql.toString());
    }

    @Override
    public void deleteLibraryConditionByIds(String[] libraryIds, String[] curatorIds, String[] userIds) throws SQLException {

        //图书馆的 library
        StringBuilder librarySql= new StringBuilder("delete from tbl_library where id in ");
        librarySql.append("(");
        for (String libraryId : libraryIds) {
            librarySql.append("'").append(libraryId).append("',");
        }
        librarySql = new StringBuilder(librarySql.substring(0, librarySql.length() - 1));
        librarySql.append(")");

        //馆长的 curator
        StringBuilder curatorSql = new StringBuilder("delete from tbl_curator where id in ");
        curatorSql.append("(");
        for (String curatorId : curatorIds) {
            curatorSql.append("'").append(curatorId).append("',");
        }
        curatorSql = new StringBuilder(curatorSql.substring(0, curatorSql.length() - 1));
        curatorSql.append(")");

        //用户的 user
        StringBuilder userSql = new StringBuilder("delete from tbl_user where id in ");
        userSql.append("(");
        for (String userId : userIds) {
            userSql.append("'").append(userId).append("',");
        }
        userSql = new StringBuilder(userSql.substring(0, userSql.length() - 1));
        userSql.append(")");

        //三条语句开启事务
        DbUtil.update(librarySql.toString(),curatorSql.toString(),userSql.toString());
    }
}
